-- =============================================
-- Author:		<Allan G. Ramirez>
-- Create date: <3/26/2012>
-- Description:	alldone-019 and ALLAN012
-- =============================================
CREATE VIEW vw_paymentrequest AS 
SELECT lineitem.projectid AS projectid, lineitem.paymentrequestid AS paymentrequestid, SUM(lineitem.paymentrequestamount) AS totalamount
FROM paymentrequestlineitem lineitem
LEFT JOIN paymentrequest request ON request.id = lineitem.paymentrequestid
GROUP BY lineitem.projectid, lineitem.paymentrequestid
GO

CREATE VIEW vw_projectpayment AS
SELECT
project.id,
project.projectid AS projectid,
project.projecttitle AS title,
project.costcenternumber AS costcenternumber,
projecttype.projecttype AS projecttypedesc,
projectstatus.statusname AS status,
region.regionname AS region,
country.countryname AS country,
project.acronym AS acronym,
project.projectstartdate AS projectstartdate,
project.projectenddate AS projectenddate,
partner.partnername AS partnername,
project.budgetoverride AS budgetoverride,
project.statusoverride AS statusoverride,
(ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) AS totalfunds,
((ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) 
	- (SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem payment WHERE payment.projectid = project.id)) AS remainingfunds
FROM
project project
LEFT JOIN projecttype projecttype ON projecttype.id = project.projecttypeid
LEFT JOIN projectstatus projectstatus ON projectstatus.id = project.projectstatusid
LEFT JOIN region region ON region.id = project.regionid
LEFT JOIN country country ON country.id = project.countryid
LEFT JOIN partner partner ON partner.id = project.partnerid
GO